package com.example.AIstudy.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.AIstudy.domain.UserSkillScore;
import lombok.Data;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.sql.Timestamp;
import java.util.List;

@Mapper
public interface UserSkillScoreDao extends BaseMapper<UserSkillScore> {
    //查询出错题本中的对应用户，在某段时间内收录的各种题型数量
    @Select("SELECT IFNULL(COUNT(cuotiji.category), 0) AS count\n" +
            "FROM (\n" +
            "    SELECT 'fanyi' AS category\n" +
            "    UNION ALL\n" +
            "    SELECT 'xuanze'\n" +
            "    UNION ALL\n" +
            "    SELECT 'yuedu'\n" +
            "    UNION ALL\n" +
            "    SELECT 'zuowen'\n" +
            ") AS categories\n" +
            "LEFT JOIN cuotiji ON cuotiji.category = categories.category \n" +
            "                  AND cuotiji.stuID = #{stuID} \n" +
            "                  AND cuotiji.insertTime >= #{startTime} \n" +
            "                  AND cuotiji.insertTime <= #{endTime} \n" +
            "GROUP BY categories.category;")
    public List<Integer>getCuotiNumByCaategory(Integer stuID, Timestamp startTime,Timestamp endTime);


    @Delete("delete  from userskillscore where stuID=#{stuID}")
    public void deleteByStuID(Integer stuID);
}
